﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace DAOTAgency
{
    public class DAO
    {
        private static TAgencyDataContext context = new TAgencyDataContext();
        
        public static IQueryable<Tour> GetAllTours()
        {
            return context.Tours;
        }
        
        public static IQueryable<Destination> GetAllDestinations()
        {
            return context.Destinations;
        }

        public static IQueryable<Transportation> GetAllTransportations()
        {
            return context.Transportations;
        }

        public static IQueryable<Board> GetAllBoards()
        {
            return context.Boards;
        }

        public static IQueryable<HotelFacility> GetAllHotelFacilities()
        {
            return context.HotelFacilities;
        }

        public static IQueryable<int> GetHotelFacilitiesByTourId(int tourId)
        {
            var hotelFacilities = from hf in context.HotelFacilitiesTours
                                  where hf.TourID == tourId
                                  select hf.HotelFacilitiesID;
            return hotelFacilities;
        }

        public static void DeleteHotelFacilitiesToursRelations(int tourId, List<int> hotelFacilitiesIds)
        {
            foreach (var item in hotelFacilitiesIds)
            {
                HotelFacilitiesTour hft = (from hf in context.HotelFacilitiesTours
                                           where hf.TourID == tourId && hf.HotelFacilitiesID == item
                                           select hf).SingleOrDefault();                
                context.HotelFacilitiesTours.DeleteOnSubmit(hft);
                context.SubmitChanges();
            }            
        }

        public static string DestinationNameById(int destinationId)
        {
            string name = (from dest in context.Destinations 
                           where dest.DestinationID == destinationId 
                           select dest.Name).SingleOrDefault();
            return name;
        }

        public static void AddTour(int destinationId, DateTime startDate, DateTime endDate, decimal price,
            string description, int? transportationId, int? boardId, List<int> hotelFacilities)
        {
            Tour newTour = new Tour()
            {
                DestinationID = destinationId,
                StartDate = startDate,
                EndDate = endDate,
                Price = price,
                Description = description,
                TransportationID = transportationId,
                BoardID = boardId,
                Reference = String.Empty,
                Visible = true
            };

            try
            {
                context.Tours.InsertOnSubmit(newTour);
                context.SubmitChanges();
                AddHotelFacilitiesToursRelations(newTour.TourID, hotelFacilities);
                string destination = (from dest in context.Destinations
                                      where dest.DestinationID == newTour.DestinationID
                                      select dest.Name).SingleOrDefault();
                newTour.Reference = destination + "-" + newTour.TourID.ToString();
                context.SubmitChanges();
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        public static void AddHotelFacilitiesToursRelations(int tourId, List<int> hotelFacilities)
        {
            try
            {
                foreach (var item in hotelFacilities)
                {
                    HotelFacilitiesTour relation = new HotelFacilitiesTour()
                    {
                        HotelFacilitiesID = item,
                        TourID = tourId
                    };
                    context.HotelFacilitiesTours.InsertOnSubmit(relation);
                }
                context.SubmitChanges();
            }
            catch (Exception e)
            {                
                throw e;
            }
        }

        public static void Save()
        {
            context.SubmitChanges();
        }

        public static void Cancel()
        {
            context = new TAgencyDataContext();
        }

        public static IQueryable<Tour> SearchInToursByDestinationId(int destinationId)
        {
            IQueryable<Tour> tours = from tour in context.Tours
                                     where tour.Visible == true && tour.DestinationID == destinationId
                                     select tour;
            return tours;
        }

        public static IQueryable<Tour> SearchInToursByPrice(decimal minPrice, decimal maxPrice, IQueryable<Tour> tours)
        {
            tours = from tour in tours
                    where minPrice <= tour.Price && tour.Price <= maxPrice
                    select tour;
            return tours;
        }

        public static IQueryable<Tour> SearchInToursByDate(DateTime startDate, DateTime endDate, IQueryable<Tour> tours)
        {
            tours = from tour in tours
                    where startDate <= tour.StartDate && tour.EndDate <= endDate
                    select tour;
            return tours;
        }

        public static IQueryable<Tour> SearchInToursByReference(string reference, IQueryable<Tour> tours)
        {
            tours = from tour in tours
                    where tour.Reference == reference
                    select tour;
            return tours;
        }

        public static IQueryable<Tour> GetTourById(int tourId)
        {
            var tour = from currnetTour in context.Tours
                        where currnetTour.TourID == tourId
                        select currnetTour;
            return tour;
        }

        public static List<string> GetHotelFacilitiesForTour(int tourId)
        {
            var facilities = GetHotelFacilitiesByTourId(tourId);
            List<string> result = new List<string>();
            foreach (int item in facilities)
            {
                string facility = (from f in context.HotelFacilities
                                where f.HotelFacilitiesID == item
                                select f.Description).SingleOrDefault();
                result.Add(facility);
            }
            return result;
        }

        public static int InsertCustomer(Customer customer, int reservationId)
        {
            Customer newCustomer = new Customer() 
            {
                FirstName = customer.FirstName,
                LastName = customer.LastName,
                Passport = customer.Passport,
                ReservationID = reservationId
            };

            context.Customers.InsertOnSubmit(newCustomer);
            context.SubmitChanges();
            return newCustomer.CustomerId;
        }

        public static int InsertReservation(int tourId, int customerId, int attenders)
        {
            decimal totalPrice = GetTourById(tourId).First().Price * (attenders + 1);
            Reservation newReservation = new Reservation()
            {
                TourID = tourId,
                TotalPrice = totalPrice,
                PaidPrice = 0,
                Reference = string.Empty,
                Discount = 0,
                CustomerID = customerId
            };
            context.Reservations.InsertOnSubmit(newReservation);
            context.SubmitChanges();
            string guarantee = (from cust in context.Customers
                                where cust.CustomerId == customerId
                                select cust.LastName).SingleOrDefault();
            context.SubmitChanges();
            return newReservation.ReservationID;
        }

        public static void UpdateReservation(int guaranteeId, int reservationId)
        {
            Reservation reservation = (from res in context.Reservations
                                       where res.ReservationID == reservationId
                                       select res).FirstOrDefault();
            string guarantee = (from cust in context.Customers
                                where cust.CustomerId == guaranteeId
                                select cust.LastName).SingleOrDefault();
            reservation.Reference = guarantee + "-" + reservation.ReservationID;
            reservation.CustomerID = guaranteeId;
            context.SubmitChanges();
        }

        public static IQueryable<Reservation> GetAllReservations()
        {
            return context.Reservations;
        }

        public static IQueryable<Reservation> ReservationFilter(string reference)
        {
            var reservations = from reservation in context.Reservations
                               where reservation.Reference.StartsWith(reference)
                               select reservation;
            return reservations;
        }

        public static void ModifyReservation(int reservationId, decimal paid, double discount)
        {
            Reservation res = (from reservation in context.Reservations
                               where reservation.ReservationID == reservationId
                               select reservation).SingleOrDefault();
            res.PaidPrice = paid;
            res.Discount = discount;
            context.SubmitChanges();
        }
    }
}
